package com.practice.chatroom;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.Stream;

import com.practice.chatroom.AppException;

/** 数据库访问对象 */
public class DAO {
    private Connection conn;

    /**
     * 连接数据库
     * 
     * @param driver   数据库驱动
     * @param constr   连接字符串
     * @param user     数据库用户名
     * @param password 数据库用户密码
     */
    public void connect(String driver, String constr, String user, String password) {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(constr, user, password);
            conn.setAutoCommit(false);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /** 断开数据库连接 */
    public void disconnect() {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询数据库
     * 
     * @param sql 查询语句
     * @return 结果集
     * @throws AppException 可能会抛给前台的错误提示
     */
    public List<Map<String, Object>> select(String sql) throws AppException {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            ResultSet rs = conn.createStatement().executeQuery(sql); // 执行查询
            ResultSetMetaData metaData = rs.getMetaData(); // 得到结果集元数据
            int columnCount = metaData.getColumnCount(); // 得到字段数据
            Getter getV[] = new Getter[columnCount];
            for (int i = 0; i < columnCount; i++) { // 遍历字段
                int columnType = metaData.getColumnType(i + 1);
                switch (columnType) { // 根据数据库字段类型，分配不同的java类型
                case Types.TINYINT:
                case Types.SMALLINT:
                case Types.INTEGER:
                case Types.BIGINT:
                    getV[i] = (r, c) -> r.getInt(c); // 整数
                    break;
                case Types.VARCHAR:
                case Types.CHAR:
                case Types.LONGVARCHAR:
                    getV[i] = (r, c) -> r.getString(c); // 字符串
                    break;
                case Types.DATE:
                case Types.TIMESTAMP:
                    getV[i] = (r, c) -> r.getDate(c); // 日期或时间
                    break;
                case Types.DECIMAL: // 小数
                    getV[i] = (r, c) -> r.getBigDecimal(c);
                    break;
                case Types.LONGVARBINARY: // 二进制字节流（如图片）
                    getV[i] = (r, c) -> (byte[]) r.getObject(c);
                    break;
                default:
                    throw new AppException("非法字段类型：" + columnType);
                }
            }
            while (rs.next()) { // 遍历结果集，按预设的类型映射，处理为map列表
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    map.put(metaData.getColumnName(i + 1), getV[i].get(rs, i + 1));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            sqlErr();
        }
        return list;
    }

    /**
     * 更新表记录
     * 
     * @param sql      更新语句
     * @param rowData  语句中的参数数据，例如要更新为的数据、条件值
     * @param colNames 语句中的参数字段名
     * @param colTypes 参数在java中的类型
     * @return 受影响行数
     * @throws AppException 可能会抛给前台的错误提示
     */
    public int update(String sql, Map<String, Object> rowData, String[] colNames, Class<?>[] colTypes)
            throws AppException {
        return updateOrDelete(sql, rowData, colNames, colTypes);
    }

    /**
     * 删除表记录
     * 
     * @param sql      删除语句
     * @param rowData  语句中的参数数据，例如要删除的条件值
     * @param colNames 语句中的参数字段名
     * @param colTypes 参数在java中的类型
     * @return 受影响行数
     * @throws AppException 可能会抛给前台的错误提示
     */
    public int delete(String sql, Map<String, Object> rowData, String[] colNames, Class<?>[] colTypes)
            throws AppException {
        return updateOrDelete(sql, rowData, colNames, colTypes);
    }

    /**
     * 删改实际统一的入口方法
     * 
     * @param sql      语句
     * @param rowData  参数数据
     * @param colNames 参数字段名
     * @param colTypes 参数字段的java类型
     * @return 受影响行数
     * @throws AppException 可能会抛给前台的错误提示
     */
    private int updateOrDelete(String sql, Map<String, Object> rowData, String[] colNames, Class<?>[] colTypes)
            throws AppException {
        return insertBatch(sql, Stream.of(rowData).collect(Collectors.toList()), colNames, colTypes, null, null);
    }

    /**
     * 批量插入。同时也是增删改实际最终的共同方法
     * 
     * @param sql        语句
     * @param rowDatas   增删改的参数数据，例如批量插入时的多行数据
     * @param colNames   参数对应的字段名
     * @param colTypes   参数在java中的类型
     * @param pkColNames 主键字段名数组（因为可能有联合主键）
     * @param newKeys    插入成功后本次数据对应的主键值，以直接修改引用的方式传入要接收的变量
     * @return 受影响行数
     * @throws AppException 可能会抛给前台的错误提示
     */
    public int insertBatch(String sql, List<Map<String, Object>> rowDatas, String[] colNames, Class<?>[] colTypes,
            String[] pkColNames, List<Map<String, Object>> newKeys) throws AppException {
        int affectedCount = 0;
        try {
            int check = 0;
            if (null != rowDatas) {
                check++;
            }
            if (null != colNames) {
                check++;
            }
            if (null != colTypes) {
                check++;
            }
            if (check > 0 && check < 3) {
                throw new AppException("参数数据与列名数据只能同时为空或同时不为空！");
            }
            boolean isNewKey = null != pkColNames;
            int isNewKParam = isNewKey ? Statement.RETURN_GENERATED_KEYS : PreparedStatement.NO_GENERATED_KEYS;
            if (check == 3) { // 常规增删改
                int colCount = colNames.length;
                if (colCount != colTypes.length) {
                    throw new AppException("列名数组与列类型数组长度不一致！");
                }
                int rowCount = rowDatas.size();
                createSetter(colNames, colTypes); // 预设为sql准备对象设置参数的方法
                for (Map<String, Object> map : rowDatas) { // 遍历增删改数据
                    PreparedStatement ps = conn.prepareStatement(sql, isNewKParam);
                    for (int i = 0; i < colCount; i++) { // 遍历参数字段
                        String colName = colNames[i];
                        Object obj = map.get(colName);
                        if (!colTypes[i].isInstance(obj)) {
                            throw new AppException("字段" + colNames[i] + "值与类型不匹配！");
                        }
                        setter.get(colName).set(ps, i + 1, obj);
                    }
                    affectedCount += ps.executeUpdate();
                    if (null != newKeys) { // 不是删改
                        newKeys(ps, pkColNames, newKeys);
                    }
                }
                if (affectedCount != rowCount) { // 批量插入时应一致
                    conn.rollback();
                    new SQLException("受影响行数（" + affectedCount + "）与操作行数（" + rowCount + "）不符！").printStackTrace();
                } else {
                    conn.commit();
                }
            } else { // 级联插入insert select的形式
                PreparedStatement ps = conn.prepareStatement(sql, isNewKParam);
                affectedCount = ps.executeUpdate();
                if (isNewKey) {
                    newKeys(ps, pkColNames, newKeys);
                }
                conn.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            sqlErr();
        }
        return affectedCount;
    }

    /**
     * 解析插入后的主键值
     * 
     * @param ps         插入语句的sql准备对象
     * @param pkColNames 主键字段名
     * @param newKeys    以直接修改引用对象的方式接收数据的变量
     * @throws AppException 可能会抛给前台的错误提示
     */
    private void newKeys(PreparedStatement ps, String[] pkColNames, List<Map<String, Object>> newKeys)
            throws AppException {
        int pkCount = pkColNames.length;
        try {
            for (int i = 0; i < pkCount; i++) {
                ResultSet newKeyRs;
                newKeyRs = ps.getGeneratedKeys();
                if (newKeyRs.next()) {
                    newKeys.add(Map.of(pkColNames[i], newKeyRs.getObject(i + 1)));
                } else {
                    throw new AppException("指定接受自增主键但获取失败！");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            sqlErr();
        }
    }

    /**
     * 预设为sql准备对象设置参数的方法
     * 
     * @param colNames 参数对应的字段名
     * @param colTypes 参数在java中的类型
     * @throws AppException 可能会抛给前台的错误提示
     */
    private void createSetter(String[] colNames, Class<?>[] colTypes) throws AppException {
        int colCount = colNames.length;
        if (colCount != colTypes.length) {
            throw new AppException("列名数组与列类型数组长度不一致！");
        }
        for (int i = 0; i < colCount; i++) { // 遍历字段
            Setter s;
            if (List.of(int.class, Integer.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setInt(j, (int) v);
            } else if (List.of(long.class, Long.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setLong(j, (long) v);
            } else if (List.of(short.class, Short.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setShort(j, (short) v);
            } else if (List.of(String.class, char.class, Character.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setString(j, String.valueOf(v));
            } else if (colTypes[i] == LocalDateTime.class) {
                s = (ps, j, v) -> ps.setTimestamp(j,
                        new Timestamp(((LocalDateTime) v).toInstant(ZoneOffset.of("+8")).toEpochMilli()));
            } else if (colTypes[i] == Timestamp.class) {
                s = (ps, j, v) -> ps.setTimestamp(j, (Timestamp) v);
            } else if (colTypes[i] == LocalDate.class || colTypes[i] == Date.class) {
                s = (ps, j, v) -> ps.setDate(j, (Date) v);
            } else if (colTypes[i] == Time.class) {
                s = (ps, j, v) -> ps.setTime(j, (Time) v);
            } else if (colTypes[i] == BigDecimal.class) {
                s = (ps, j, v) -> ps.setBigDecimal(j, (BigDecimal) v);
            } else if (List.of(double.class, Double.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setDouble(j, (double) v);
            } else if (List.of(float.class, Float.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setFloat(j, (float) v);
            } else if (List.of(boolean.class, Boolean.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setBoolean(j, (boolean) v);
            } else if (List.of(byte.class, Byte.class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setByte(j, (byte) v);
            } else if (List.of(byte[].class, Byte[].class).contains(colTypes[i])) {
                s = (ps, j, v) -> ps.setBytes(j, (byte[]) v);
            } else {
                throw new AppException("非法字段类型：" + colTypes[i]);
            }
            setter.put(colNames[i], s);
        }
    }

    /**
     * 统一抛sql异常
     * 
     * @throws AppException 可能会抛给前台的错误提示
     */
    private void sqlErr() throws AppException {
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        throw new AppException("发生异常！");
    }

    @FunctionalInterface
    /** 遍历结果集前根据字段类型预设好取值方法 */
    interface Getter {
        Object get(ResultSet r, int c) throws SQLException;
    }

    @FunctionalInterface
    /** 遍历参数前根据字段类型预设好设置参数的方法 */
    interface Setter {
        void set(PreparedStatement ps, int c, Object v) throws SQLException;
    }

    /**
     * 本次sql的参数设置映射
     * 
     * @键 字段名
     * @值 设置参数的lambda
     */
    private Map<String, Setter> setter = new HashMap<>();
}